package com.example.excel.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;

@Component
@Slf4j
public class ExcelUtil {

    /**
     * 动态选择模板进行解析
     * @param fileName
     * @param stream
     * @return
     * @throws IOException
     */
    public static Workbook init(String fileName,InputStream stream) throws IOException {
        if (fileName.toLowerCase().endsWith(".xls")) {
            return new HSSFWorkbook(stream);
        }
        return new XSSFWorkbook(stream);

    }


    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //判断数据的类型
        switch (cell.getCellType()){
            //数字
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 自定义日期格式
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
                    cellValue = dateFormat.format(cell.getDateCellValue());
                }else {
                    cell.setCellType(CellType.STRING);
                    cellValue = cell.getStringCellValue();
                }
                break;
            //字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case BLANK:
                cellValue = "";
                break;
            //故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 背景颜色自定义的单元格样式
     * @param workbook
     * @return
     */
    public static CellStyle getCustomBackgroundStyle(Workbook workbook,short index) {
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        // 是设置前景色不是背景色
        style.setFillForegroundColor(index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 设置批注
     * @param workbook
     * @param sheet 工作簿
     * @param cell 单元格
     * @param cellStyle 单元格样式
     * @param rowIndex 单元格行
     * @param colIndex 单元格列
     * @param tip 提示信息
     */
    public static void setAnchor(Workbook workbook,
                                 Sheet sheet,
                                 Cell cell,
                                 CellStyle cellStyle,
                                 int rowIndex,
                                 int colIndex,
                                 String tip){
        //如果单元格为空，需要创建单元格，否则空指针异常
        if(cell == null){
            Row row = sheet.getRow(rowIndex);
            if(row == null) {
                row = sheet.createRow(rowIndex);
            }
            cell = row.createCell(colIndex);
        }
        cell.setCellStyle(cellStyle);
        if (cell.getCellComment() != null) {
            // 有批注则修改
            Comment comment = cell.getCellComment();
            if(workbook instanceof HSSFWorkbook){
                comment.setString(new HSSFRichTextString(tip));
            }else {
                comment.setString(new XSSFRichTextString(tip));
            }

        } else {
            // 没有则新增
            Drawing drawing = sheet.createDrawingPatriarch();
            Comment comment = null;
            if(workbook instanceof HSSFWorkbook){
                comment = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
                        (short) cell.getColumnIndex(),
                        cell.getRowIndex(),
                        (short) (cell.getColumnIndex() + 3),
                        cell.getRowIndex() + 4));
                comment.setString(new HSSFRichTextString(tip));
            }else {
                comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
                        (short) cell.getColumnIndex(),
                        cell.getRowIndex(),
                        (short) (cell.getColumnIndex() + 3),
                        cell.getRowIndex() + 4));
                comment.setString(new XSSFRichTextString(tip));
            }
            cell.setCellComment(comment);
        }
    }

    /**
     * 保存
     * @param workbook
     * @param storagePath 存储路径
     * @param fileName 文件名（不需要后缀名）
     * @throws IOException
     */
    public static void save(Workbook workbook,String storagePath,String fileName) throws IOException {
        if(workbook instanceof HSSFWorkbook){
            fileName = fileName + ".xls";
        }else {
            fileName = fileName + ".xlsx";
        }
        FileOutputStream out = null;
        out = new FileOutputStream(storagePath+fileName);
        workbook.write(out);
        out.close();
        workbook.close();

    }

    public static void save(Workbook workbook,String storagePath) throws IOException {
        FileOutputStream out = null;
        out = new FileOutputStream(storagePath);
        workbook.write(out);
        out.close();
        workbook.close();

    }

    /**
     * 列头单元格样式
     *
     * @param workbook            工作簿
     * @param fontBold            字体加粗
     * @param isBorder            边框
     * @param horizontalAlignment 水平对齐
     * @return 结果
     */
    public static HSSFCellStyle getColumnStyle(HSSFWorkbook workbook, boolean fontBold, boolean isBorder, HorizontalAlignment horizontalAlignment) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //设置字体名字
        font.setFontName("宋体");
        font.setBold(fontBold);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        if (isBorder) {
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(IndexedColors.BLACK.index);
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(IndexedColors.BLACK.index);
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(IndexedColors.BLACK.index);
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(IndexedColors.BLACK.index);
        }
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(horizontalAlignment);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 列头单元格样式
     *
     * @param workbook            工作簿
     * @param fontBold            字体加粗
     * @param isBorder            边框
     * @param horizontalAlignment 水平对齐
     * @return 结果
     */
    public static HSSFCellStyle getColumnStyle(HSSFWorkbook workbook,String fontName,short fontSize, boolean fontBold, boolean isBorder, HorizontalAlignment horizontalAlignment) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints(fontSize);
        //设置字体名字
        font.setFontName(fontName);
        font.setBold(fontBold);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        if (isBorder) {
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(IndexedColors.BLACK.index);
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(IndexedColors.BLACK.index);
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(IndexedColors.BLACK.index);
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(IndexedColors.BLACK.index);
        }
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(horizontalAlignment);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }


    public static void download(HttpServletResponse response, HSSFWorkbook workbook, String importFileName){
        try {
            String fileName = importFileName;
            String codeFileName = new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1");
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + codeFileName);
            OutputStream out = response.getOutputStream();
            workbook.write(response.getOutputStream());
            out.flush();
            out.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void setCellValue(Sheet sheet,int rowIndex ,int columnIndex,String msg) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            cell = row.createCell(columnIndex);
        }
        cell.setCellValue(msg);
    }
}
